import re
import time
import json
from flask import request
from flask_restful import abort
from flask_login import current_user, login_user
from funcy import project
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.exc import IntegrityError
from funcy import partial

from redash import models, limiter
from redash.permissions import (
    require_permission,
    require_admin_or_owner,
    is_admin_or_owner,
    require_permission_or_owner,
    require_admin_default,
)
from redash.handlers.base import (
    BaseResource,
    require_fields,
    get_object_or_404,
    paginate,
    order_results as _order_results,
)

from redash.authentication.account import (
    invite_link_for_user,
    send_invite_email,
    send_password_reset_email,
    send_verify_email,
)
from redash.settings import parse_boolean
from redash import settings

class ContractSealNewResource(BaseResource):
    def get(self):
        result = models.ContractSeal.get_id()
        return result[0]

    @require_admin_default
    def post(self):
        req = request.get_json(force=True)
        require_fields(req, ("code","name"))
        employeeid = req["employeeid"] if len(str(req["employeeid"]))>0 else 0
        sealcount = req["sealcount"] if len(str(req["sealcount"]))>0 else 0
        handledept = req["handledept"] if len(str(req["handledept"]))>0 else 0
        departmentid = req["departmentid"] if len(str(req["departmentid"]))>0 else 0

        contractseal = models.ContractSeal(
        id=req["id"],
        org=self.current_org,
        userid=req["userid"],
        bill_date = req["bill_date"],
        contractsid = req["contractsid"],
        code = req["code"],
        name = req["name"],
        status =req["status"],
        message =req["message"],
        sourcetype =req["sourcetype"],
        sourceid =req["sourceid"],
        employeeid=employeeid,
        sealdate=req["sealdate"],
        sealcount=sealcount,
        handledept= handledept,
        departmentid= departmentid,
        archive = req["archive"],
        )
        contractactivity = models.ContractActivity(
        userid=req["userid"],
        contractsid = req["contractsid"],
        message ="合同用印",
        sourcetype ="contractseal",
        sourceid =req["id"],
        )
        try:
            models.db.session.add(contractseal)
            models.db.session.add(contractactivity)
            models.db.session.commit()
        except IntegrityError as e:
            if "code" in str(e):
                abort(400, message="编号已存在.")
            abort(500)
        try:
            strsql = """select  value from public.globalsetting where  type='接口' and code='合同生效回写HRP' and value='1'"""
            result = models.Contracts.get_selcontracts(strsql)
            if result is None :
                return ;
        except :
            abort(400, message=strsql)

        try:
            strsql ="""select t1.code,t1.officialcode,t1.name,cast(date_part('year', t1.bill_date) as varchar(4))as intyear,cast(date_part('month', t1.bill_date) as varchar(2)) as period,to_char(t1.bill_date,'YYYY-MM-DD') as bill_date,
            cast(t1.amount as varchar(20)) as amount,t2.strcustomercode,COALESCE(t3.strcustomercode,'')  as strcode3,t1.contracttype,case when t1.pay_direction=1 then '付款' else '收款'end as pay_direction,
            t4.code as orgunitcode,t5.code as usercode
            from contracts t1 left join customertemp t2 on t1.party_aid=t2.id left join customertemp t3 on t1.party_bid=t3.id 
            left join orgunit t4 on t1.departmentid =t4.id left join users t5 on t1.userid=t5.id
            where t1.id="""+str(req["contractsid"])
            result = models.db.session.execute(strsql).fetchone()

            hrpsql="""
                    insert into CONTRACT (LNGCONTRACTID, INTYEAR, BYTPERIOD, LNGORGANIZATIONID, LNGTEMPLATEID, 
                    LNGBACKTEMPLATEID, STRCONTRACTNAME, STRDATE, STRCONTRACTCODE, LNGCUSTOMER1ID,
                    LNGCUSTOMER2ID, STRCONTACT1, STRCONTACT2, STRAGENT1, STRCUSTOMER1, 
                    STRCUSTOMER2, LNGCONTRACTGROUPID, LNGCONTRACTTYPEID, LNGCONTRACTOPTIONID, STRSTARTDATE, 
                    STRENDDATE, DBLPURCHASEAMOUNT, DBLSALEAMOUNT, STRTRAFFICMETHOD, STRADDRESS, 
                    LNGDEPARTMENTID, LNGEMPLOYEEID, LNGCLASSID1, LNGCLASSID2, LNGOPERATOR1ID, 
                    STRFLOWDATE, LNGOPERATOR2ID, STRDUTEDATE, LNGOPERATOR3ID, STROKDATE, 
                    STRSTATE, STRMEMO, STROTHER, DBLSUMACCOUNT, BLNCONFIRM, 
                    BLNZERO, BLNOK, BLNCOMPLETE, BLNCHAGE, STRRESERVE1, 
                    STRRESERVE2, STRRESERVE3, STRRESERVE4, STRRESERVE5, STRRESERVE6, 
                    DBLRESERVE1, DBLRESERVE2, DATERESERVE1, DATERESERVE2)
                    select contract_seq.nextval, """+result.intyear+""", """+result.period+""", 1, 10161, 
                    10162, '"""+ result.officialcode + result.name+"""', '"""+result.bill_date+"""', '"""+result.code+"""', (select nvl(min(lngcustomerid),0) From customer where strcustomercode='"""+result.strcustomercode+"""'), 
                    (select nvl(min(lngcustomerid),0) From customer where strcustomercode='"""+result.strcode3+"""'), ' ', ' ', ' ', ' ', 
                    ' ', (select nvl(min(lngcontractgroupid),0) From contractgroup), (select nvl(min(lngcontracttypeid),0) From CONTRACTTYPE), (select nvl(min(lngcontractoptionid),0) From ContractOption where ContractOptionDir='"""+result.pay_direction+"""'), '"""+result.bill_date+"""',
                    '"""+result.bill_date+"""', 0, 0, ' ', ' ', 
                    (select nvl(min(LNGDEPARTMENTID),0) From department where strdepartmentcode='"""+result.orgunitcode+"""'), (select nvl(min(LNGemployeeID),0) From employee where stremployeecode='"""+result.usercode+"""'), 0, 0, 1,
                    '"""+result.bill_date+"""', 1, '"""+result.bill_date+"""', 0, ' ', 
                    '未结案', '"""+result.officialcode+"""', ' ', 0, 2,
                    0, 1, 0, 0, ' ',
                    ' ', ' ', ' ', ' ', ' ', 
                    0, 0, '"""+result.bill_date+"""', '"""+result.bill_date+"""'
                    from dual WHERE NOT EXISTS(SELECT 'X' FROM CONTRACT WHERE STRCONTRACTCODE='"""+result.code+"""')"""
            hrpsql2="""insert into ContractPlan (LNGCONTRACTPLANID, LNGCONTRACTID, STRDATE, LNGPAYMENTMETHODID, DBLPRICE, STRMEMO)
                    select ContractPlan_seq.Nextval, (select LNGCONTRACTID from CONTRACT where STRCONTRACTCODE='"""+result.code+"""'), 
                    '"""+result.bill_date+"""', 1, """+result.amount+""", ' ' from dual"""
            strsql="""
                select STRCONTRACTNAME from CONTRACT where STRCONTRACTCODE='"""+result.code+"""'"""
            data_source = models.DataSource.get_by_name("jsphrp")
            result, error = data_source.query_runner.run_query(strsql, self.current_user)
            if error :
                return str(error);
            json_object = json.loads(result)
            if len(json_object["rows"])>0 :
                return ;
            try:
                data_source = models.DataSource.get_by_name("jsphrp")
                result, error = data_source.query_runner.run_query(hrpsql, self.current_user)
                if error:
                    return str(error)+':'+hrpsql;
            except :
                data_source = models.DataSource.get_by_name("jsphrp")
                result, error = data_source.query_runner.run_query(hrpsql2, self.current_user)
                if error:
                    return str(error)+':'+hrpsql;
        except :
            return;

class ContractSealResource(BaseResource):
    def get(self, contractseal_id):
        result = models.ContractSeal.get_by_id(contractseal_id)
        return result.to_dict()

    @require_admin_default
    def post(self, contractseal_id):
        req = request.get_json(force=True)
        require_fields(req, ("code","name"))
        employeeid = req["employeeid"] if len(str(req["employeeid"]))>0 else 0
        sealcount = req["sealcount"] if len(str(req["sealcount"]))>0 else 0
        handledept = req["handledept"] if len(str(req["handledept"]))>0 else 0
        departmentid = req["departmentid"] if len(str(req["departmentid"]))>0 else 0

        contractseal = models.ContractSeal.get_by_id(contractseal_id)
        params = dict(bill_date=req["bill_date"], contractsid=req["contractsid"], code=req["code"],
                name=req["name"],sourceid=req["sourceid"],handledept= handledept,archive = req["archive"],
                sourcetype=req["sourcetype"],message=req["message"],departmentid= departmentid,
                employeeid=employeeid,sealdate=req["sealdate"],sealcount=sealcount)
        try:
            self.update_model(contractseal, params)
            models.db.session.commit()
        except IntegrityError as e:
            if "code" in str(e):
                abort(400, message="编号已存在.")
            abort(500)
        try:
            strsql = """select  value from public.globalsetting where  type='接口' and code='合同生效回写HRP' and value='1'"""
            result = models.Contracts.get_selcontracts(strsql)
            if result is None :
                return ;
        except :
            abort(400, message=strsql)

        try:
            strsql ="""select t1.code,t1.name,cast(date_part('year', t1.bill_date) as varchar(4))as intyear,cast(date_part('month', t1.bill_date) as varchar(2)) as period,to_char(t1.bill_date,'YYYY-MM-DD') as bill_date,
            cast(t1.amount as varchar(20)) as amount,t2.strcustomercode,COALESCE(t3.strcustomercode,'')  as strcode3,t1.contracttype,case when t1.pay_direction=1 then '付款' else '收款'end as pay_direction,
            t4.code as orgunitcode,t5.code as usercode
            from contracts t1 left join customertemp t2 on t1.party_aid=t2.id left join customertemp t3 on t1.party_bid=t3.id 
            left join orgunit t4 on t1.departmentid =t4.id left join users t5 on t1.userid=t5.id
            where t1.id="""+str(req["contractsid"])
            result = models.db.session.execute(strsql).fetchone()

            hrpsql="""
                    insert into CONTRACT (LNGCONTRACTID, INTYEAR, BYTPERIOD, LNGORGANIZATIONID, LNGTEMPLATEID, 
                    LNGBACKTEMPLATEID, STRCONTRACTNAME, STRDATE, STRCONTRACTCODE, LNGCUSTOMER1ID,
                    LNGCUSTOMER2ID, STRCONTACT1, STRCONTACT2, STRAGENT1, STRCUSTOMER1, 
                    STRCUSTOMER2, LNGCONTRACTGROUPID, LNGCONTRACTTYPEID, LNGCONTRACTOPTIONID, STRSTARTDATE, 
                    STRENDDATE, DBLPURCHASEAMOUNT, DBLSALEAMOUNT, STRTRAFFICMETHOD, STRADDRESS, 
                    LNGDEPARTMENTID, LNGEMPLOYEEID, LNGCLASSID1, LNGCLASSID2, LNGOPERATOR1ID, 
                    STRFLOWDATE, LNGOPERATOR2ID, STRDUTEDATE, LNGOPERATOR3ID, STROKDATE, 
                    STRSTATE, STRMEMO, STROTHER, DBLSUMACCOUNT, BLNCONFIRM, 
                    BLNZERO, BLNOK, BLNCOMPLETE, BLNCHAGE, STRRESERVE1, 
                    STRRESERVE2, STRRESERVE3, STRRESERVE4, STRRESERVE5, STRRESERVE6, 
                    DBLRESERVE1, DBLRESERVE2, DATERESERVE1, DATERESERVE2)
                    select contract_seq.nextval, """+result.intyear+""", """+result.period+""", 1, 10161, 
                    10162, '"""+result.name+"""', '"""+result.bill_date+"""', '"""+result.code+"""', (select nvl(min(lngcustomerid),0) From customer where strcustomercode='"""+result.strcustomercode+"""'), 
                    (select nvl(min(lngcustomerid),0) From customer where strcustomercode='"""+result.strcode3+"""'), ' ', ' ', ' ', ' ', 
                    ' ', (select nvl(min(lngcontractgroupid),0) From contractgroup), (select nvl(min(lngcontracttypeid),0) From CONTRACTTYPE), (select nvl(min(lngcontractoptionid),0) From ContractOption where ContractOptionDir='"""+result.pay_direction+"""'), '"""+result.bill_date+"""',
                    '"""+result.bill_date+"""', 0, 0, ' ', ' ', 
                    (select nvl(min(LNGDEPARTMENTID),0) From department where strdepartmentcode='"""+result.orgunitcode+"""'), (select nvl(min(LNGemployeeID),0) From employee where stremployeecode='"""+result.usercode+"""'), 0, 0, 1,
                    '"""+result.bill_date+"""', 1, '"""+result.bill_date+"""', 0, ' ', 
                    '未结案', ' ', ' ', 0, 2,
                    0, 1, 0, 0, ' ',
                    ' ', ' ', ' ', ' ', ' ', 
                    0, 0, '"""+result.bill_date+"""', '"""+result.bill_date+"""'
                    from dual WHERE NOT EXISTS(SELECT 'X' FROM CONTRACT WHERE STRCONTRACTCODE='"""+result.code+"""')"""
            hrpsql2="""insert into ContractPlan (LNGCONTRACTPLANID, LNGCONTRACTID, STRDATE, LNGPAYMENTMETHODID, DBLPRICE, STRMEMO)
                    select ContractPlan_seq.Nextval, (select LNGCONTRACTID from CONTRACT where STRCONTRACTCODE='"""+result.code+"""'), 
                    '"""+result.bill_date+"""', 1, """+result.amount+""", ' ' from dual"""
            strsql="""
                select STRCONTRACTNAME from CONTRACT where STRCONTRACTCODE='"""+result.code+"""'"""
            data_source = models.DataSource.get_by_name("jsphrp")
            result, error = data_source.query_runner.run_query(strsql, self.current_user)
            if error :
                return str(error);
            json_object = json.loads(result)
            if len(json_object["rows"])>0 :
                return ;
            try:
                data_source = models.DataSource.get_by_name("jsphrp")
                result, error = data_source.query_runner.run_query(hrpsql, self.current_user)
                if error:
                    return str(error)+':'+hrpsql;
            except :
                data_source = models.DataSource.get_by_name("jsphrp")
                result, error = data_source.query_runner.run_query(hrpsql2, self.current_user)
                if error:
                    return str(error)+':'+hrpsql2;
        except :
           return; 


class ContractSealdeleteResource(BaseResource):
    @require_admin_default
    def post(self, contractseal_id):
        contractseal =  models.ContractSeal.get_by_id(contractseal_id)
        contractactivity = models.ContractActivity.get_by_sourceid("contractseal",contractseal_id)
        models.db.session.delete(contractseal)
        models.db.session.delete(contractactivity)
        models.db.session.commit()

class ContractSealListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        code = req["code"]
        name = req["name"]
        contractcode = req["contractcode"]
        contractname = req["contractname"]
        departmentid= req["departmentid"]
        party_a = req["party_a"] if "party_a" in req else ""
        seltype = req["seltype"] if "seltype" in req else "1"
        type = req["type"]
        userid = str(req["userid"])
        orderfield = req["field"] if "field" in req else ""
        if orderfield=="code" :
        #     orderfield ="t1.code"
        # elif orderfield=="contractcode":
            orderfield ="t2.code"
        elif orderfield=="contractname":
            orderfield ="t2.name"
        elif orderfield=="name":
            orderfield ="t1.name"
        elif orderfield=="partyaname":
            orderfield ="t3.strcustomername"
        elif orderfield=="typename":
            orderfield ="t4.typename"
        elif orderfield=="amount":
            orderfield ="t2.amount"
        elif orderfield=="employeename":
            orderfield ="t5.name"
        elif orderfield=="strsealdate":
            orderfield ="t1.sealdate"
        else:
            orderfield ="t2.code"
        order = req["order"] if "order" in req else 0

        strwhere ="1=1" 
        if code !="" :
            strwhere = strwhere + " and t1.code like '%"+code+"%'"
        if name !='' :
            strwhere = strwhere + " and t1.name like '%"+name+"%'"
        if contractcode !='' :
            strwhere = strwhere + " and t2.officialcode like '%"+contractcode+"%'"
        if contractname !='' :
            strwhere = strwhere + " and t2.name like '%"+contractname+"%'"
        if party_a !='' :
            strwhere = strwhere + " and t3.strcustomername like '%"+party_a+"%'"
        if type !='' :
            strwhere = strwhere + " and  '"+type+"' = ANY(t2.type)"
        # if userid !='' and seltype !='1':
        #     strwhere = strwhere + """ and  (t1.userid='""" + userid + """' or  t1.userid in (select  users.id from
        #         (with RECURSIVE T1 (keys,items) as
        #         (select   (items)->>'key' as keys,json_array_elements((items)->'children') as items,'0' as parentkeys
        #          from   (with RECURSIVE le (keys,items,parentkeys) as
        #         (select   (items)->>'key' as keys,json_array_elements((items)->'children') as items,'0' as parentkeys
        #          from   (select unnest(details) items From orgstruct where orgstruct_enum='admin' and expire_date is null) AA
        #          union all
        #          select (items)->>'key' as keys,json_array_elements((items)->'children') as items,keys as parentkeys from le)
        #         select le.* from le inner join users on to_number(le.keys, '9999999999999999999')= users.orgunit_id where users.id='""" + userid + """') AA
        #          union all
        #          select (items)->>'key' as keys,json_array_elements((items)->'children') as items,keys as parentkeys from T1)
        #         select * from T1
        #         union all
        #         (with RECURSIVE le (keys,items,parentkeys) as
        #         (select   (items)->>'key' as keys,json_array_elements((items)->'children') as items,'0' as parentkeys
        #          from   (select unnest(details) items From orgstruct where orgstruct_enum='admin' and expire_date is null) AA
        #          union all
        #          select (items)->>'key' as keys,json_array_elements((items)->'children') as items,keys as parentkeys from le)
        #         select le.* from le inner join users on to_number(le.keys, '9999999999999999999')= users.orgunit_id where users.id='""" + userid + """'))T2
        #         inner join users on to_number((T2.items)->>'key', '9999999999999999999')=users.orgunit_id
        #         union all select users.id from public.users where users.orgunit_id in(select orgunit_id from public.users where id='""" + userid + """')))"""

        strgetfield =""" trim(COALESCE(to_char(t1.id,'999999999'),'daz' || t2.id)) id,t1.userid,t1.bill_date,t1.contractsid,t1.code,t1.name,t1.message,t1.status,t1.updated_at,t1.created_at,t1.sourcetype,
        t1.sourceid,t1.departmentid,t1.org_id,t1.employeeid,t1.sealdate,t1.sealcount,t1.handledept,t1.archive,t2.officialcode as contractcode,t2.name  as contractname,
        COALESCE(to_char(t1.sealdate,'YYYY年MM月DD日'),'') as strsealdate,case when t1.archive then '是' else '否' end as strarchive,t2.pricetype,
        t3.strcustomername as partyaname,t4.typename,t5.name as employeename,t6.name as jbname,COALESCE(t1.id,t2.id) as dazid,t2.amount,
                    case when t1.status=0 then '用印草稿' when t1.status=1 then '用印审核中' when t1.status=2 then '用印已审核' else '未用印' end strstatus,t8.name as orgname"""
        if seltype =='1' :
            tblname = """public.contracts t2 left join public.contractseal t1 on t2.id = t1.contractsid
            inner join public.orgunit t8 on t2.departmentid=t8.id
            left join public.customertemp t3 on t2.party_aid=t3.id  left join
                    public.contracttypeview t4 on t2.type =t4.type  and t2.org_id=t4.org_id
                    left join public.users t5 on t1.employeeid = t5.id
                    left join public.users t6 on t1.handledept=t6.id"""
            if len(departmentid)>0 :
                departid=','.join(str(i) for i in departmentid)
                departid='(' + departid +')'
                strwhere = strwhere + " and t2.departmentid in " +departid
            strwhere = strwhere + """ and t2.status=2 and COALESCE(t1.id,0)=0 """
        else:
            tblname = """public.contractseal t1 inner join public.contracts t2 on t1.contractsid=t2.id
            left join public.orgunit t8 on t1.departmentid=t8.id
            left join public.customertemp t3 on t2.party_aid=t3.id  left join
                    public.contracttypeview t4 on t2.type =t4.type  and t2.org_id=t4.org_id
                    left join public.users t5 on t1.employeeid = t5.id
                    left join public.users t6 on t1.handledept=t6.id"""
            if len(departmentid)>0 :
                departid=','.join(str(i) for i in departmentid)
                departid='(' + departid +')'
                strwhere = strwhere + " and t1.departmentid in " +departid
            strwhere = strwhere + """ and t1.id >0 """
            if seltype =='3' :
                strwhere = strwhere + """ and t1.archive=false """
            if seltype =='4' :
                strwhere = strwhere + """ and t1.archive=true """
        #orderfield ="t1.id"
        result = models.ContractApply.get_pagequery(tblname,strgetfield,orderfield,page_size,page,order,strwhere)
        return result[0]
